from db.db_util import dataframe_from_db
from util.common import *

if __name__ == '__main__':

    w_sql_list = []

    # df = dataframe_from_db("""
    #     select * from tf_sa_compliance_task
    # """)
    # filtered_df = df[df['ACTUAL_START_TIME'].notna()]

    df = dataframe_from_db("""
           select * from tf_vu_hole_task
       """)
    filtered_df = df[df['CREATE_TIME'].notna()]

    df2 = dataframe_from_db("""
         select * from tl_log_user_copy1
     """)
    records = df2.to_dict(orient='records')
    keys = [item['LOG_SUB_CATAGORY'] for item in records]
    my_dict = dict(zip(keys, records))

    for row in filtered_df.itertuples():
        # start_date = row.ACTUAL_START_TIME
        start_date = row.CREATE_TIME

        item = row.HOLE_TYPE
        # if item != '3' and item != '4':
        #     continue
        module = {}
        if item == '1':
            module = my_dict[32]
        elif item == '2':
            module = my_dict[33]
        elif item == '3':
            module = my_dict[30]
        elif item == '4':
            module = my_dict[31]

        if 'LOG_CATAGORY' not in module:
            continue
        log = {
            "id": "USRLZX" + random_string(14),
            "userAccountId": "PORT6452766823027939",
            "userAccountName": "3yangjing16",
            "userName": "3yangjing16",
            "sessionId": "E5D410A2F03C769FF2E7D13FC231F1A3",
            "ip": "10.44.105.7",
            "port": None,
            "level": 0,
            "levelDesc": "正常",
            "optCode": "1",
            "result": 1,
            "opTime": start_date,
            "saveTime": start_date,
            "logDescMac": None,
            "catagory": module['LOG_CATAGORY'],
            "subCatagory": module['LOG_SUB_CATAGORY'],
            "catagoryDesc": module['LOG_CATAGORY_DESC'],
            "subCatagoryDesc": module['LOG_SUB_CATAGORY_DESC'],
            "module": module['MODULE_NAME'],
            "moduleCode": module['MOD_CODE'],
            "desc": module['LOG_DESC'],
        }
        message3 = render_template(
            """
                        INSERT INTO TL_LOG_USER (USER_LOG_ID,
                             USER_ACCOUNT_ID,
                             USER_ACCOUNT_NAME,
                             USER_NAME,
                             SESSION_ID,
                             USER_IP,
                             USER_PORT,
                             MODULE_NAME,
                             LOG_DESC,
                             LOG_LEVEL,
                             LOG_LEVEL_DESC,
                             LOG_CATAGORY,
                             LOG_CATAGORY_DESC,
                             LOG_SUB_CATAGORY,
                             LOG_SUB_CATAGORY_DESC,
                             MOD_CODE,
                             OP_CODE,
                             OP_RESULT,
                             OP_TIME,
                             SAVE_TIME,
                             LOG_DESC_MAC)
                        values ($id,
                                $userAccountId,
                                $userAccountName,
                                $userName,
                                $sessionId,
                                $ip,
                                $port,
                                $module,
                                $desc,
                                $level,
                                $levelDesc,
                                $catagory,
                                $catagoryDesc,
                                $subCatagory,
                                $subCatagoryDesc,
                                $moduleCode,
                                $optCode,
                                $result,
                                $opTime,
                                $saveTime,
                                $logDescMac
        );
        """, log)
        w_sql_list.append(message3)

    write_file('target/SATP_系统日志-2.sql', w_sql_list)
